package br.com.registrofinancas.database;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import br.com.registrofinancas.model.CategoryVO.CategoryType;
import br.com.registrofinancas.model.IncomeVO;
import br.com.registrofinancas.util.UsuarioUtils;
import android.content.Context;
import android.database.Cursor;

public class MovimentosEntradaDAO extends MovimentosDAO {
	protected static final String INSERT = "INSERT INTO " + TABELA + "(id_categoria, valor, saldo, data, observacao, latitude, longitude) VALUES (?,?,?,?,?,?,?)";
	protected static final String UPDATE = "UPDATE " + TABELA + " SET id_categoria = ?, valor = ?, saldo =?, data = ?, observacao = ? WHERE id = ?";
	public MovimentosEntradaDAO(Context contexto) {
		super(contexto);
	}
	
	public long insert(IncomeVO entrada) {
		this.insertStatement = this.db.compileStatement(INSERT);		
		this.insertStatement.bindLong(1, entrada.getCategory().getId());
		this.insertStatement.bindDouble(2, entrada.getValue());
		this.insertStatement.bindDouble(3, entrada.getValue());
		this.insertStatement.bindString(4, FORMATA_DATA.format(entrada.getOperationDate()));
		this.insertStatement.bindString(5, entrada.getDescription());
		if (null != entrada.getLatitude()){
			this.insertStatement.bindDouble(6, entrada.getLatitude());
		}
		if (null != entrada.getLongitude()){
			this.insertStatement.bindDouble(7, entrada.getLongitude());
		}
		return insertStatement.executeInsert();
	}	
	
	public long update(IncomeVO entrada){
		this.insertStatement = this.db.compileStatement(UPDATE);		
		this.insertStatement.bindLong(1, entrada.getCategory().getId());
		this.insertStatement.bindDouble(2, entrada.getValue());
		this.insertStatement.bindDouble(3, entrada.getSaldo());
		this.insertStatement.bindString(4, FORMATA_DATA.format(entrada.getOperationDate()));
		this.insertStatement.bindString(5, entrada.getDescription());
		this.insertStatement.bindLong(6, entrada.getId());
		return insertStatement.executeUpdateDelete();		
	}
	
	public IncomeVO getEntradaPorId( Integer id ) {
		IncomeVO entrada = null;
		Cursor cursor = 
				this.db.query( TABELA, new String[ ] { "id", "id_categoria", "valor", "saldo", "data", "observacao", "latitude", "longitude" }, 
							   "id = ?", new String[]{id.toString()}, null, null, "id" ) ;
		
		if (cursor.moveToFirst( )) {
				
			try {
				entrada = new IncomeVO( ) ;
				entrada.setId( cursor.getInt( 0 ) ) ;
				
				CategoriasDAO categoriaDAO = new CategoriasDAO(contexto);
				entrada.setCategory( categoriaDAO.selectById(cursor.getInt( 1 )) ) ;
				
				entrada.setValue( cursor.getDouble( 2 ) ) ;
				entrada.setSaldo( cursor.getDouble( 3 ) ) ;
				entrada.setOperationDate( FORMATA_DATA.parse(cursor.getString( 4 )) ) ;
				entrada.setDescription( cursor.getString( 5 ) ) ;
				entrada.setLatitude(cursor.getDouble(6));
				entrada.setLongitude(cursor.getDouble(7));
			} catch (ParseException e) {
				e.printStackTrace();
			}

		}
		if (null != cursor && !cursor.isClosed( )) {
			cursor.close( ) ;
		}
		return entrada ;
	}	

	public List<IncomeVO> getEntradas( ) {
		List<IncomeVO> list = new ArrayList<IncomeVO>( ) ;

		StringBuilder query = new StringBuilder();
		query.append("SELECT CRE.id, CRE.id_categoria, CRE.valor, CRE.saldo, CRE.data, CAT.descricao, CRE.latitude, CRE.longitude\n");
		query.append("FROM MOVIMENTOS CRE\n");
		query.append("INNER JOIN CATEGORIAS CAT ON CAT.id = CRE.id_categoria\n");
		query.append("WHERE CAT.tipo = ?\n");
		query.append("  AND CAT.id_usuario = ?\n");
		
		String[] parametros = {	String.valueOf(CategoryType.INCOME.ordinal()),
								UsuarioUtils.getIdUsuario().toString()};
		
		Cursor cursor = this.db.rawQuery(query.toString(), parametros);
		
		if (cursor.moveToFirst( )) {
			CategoriasDAO categoriaDAO = new CategoriasDAO(contexto);
			do {
				try {
					IncomeVO movimento = new IncomeVO( ) ;
					movimento.setId( cursor.getInt( 0 ) ) ;
					movimento.setCategory( categoriaDAO.selectById(cursor.getInt( 1 )) ) ;
					movimento.setValue( cursor.getDouble( 2 ) ) ;
					movimento.setSaldo( cursor.getDouble( 3 ) ) ;
					movimento.setOperationDate(FORMATA_DATA.parse(cursor.getString( 4 )));
					movimento.setDescription( cursor.getString( 5 ) ) ;
					movimento.setLatitude(cursor.getDouble(6));
					movimento.setLongitude(cursor.getDouble(7));					
					list.add( movimento ) ;
				} catch (ParseException e) {
					e.printStackTrace();
				}
					
			}
			while (cursor.moveToNext( )) ;
		}
		
		if (null != cursor && !cursor.isClosed( )) {
			cursor.close( ) ;
		}
		
		return list ;
	}	
	
		
	public List<IncomeVO> getEntradasPorMes( Date data ) {
		List<IncomeVO> list = new ArrayList<IncomeVO>( ) ;
		StringBuilder query = new StringBuilder();
		query.append("SELECT MOV.id, MOV.id_categoria, MOV.valor, MOV.saldo, MOV.data, MOV.observacao, MOV.latitude, MOV.longitude\n");
		query.append("FROM MOVIMENTOS MOV\n");
		query.append("JOIN CATEGORIAS CAT ON CAT.id = MOV.id_categoria\n");
		query.append("WHERE SUBSTR(data,1,7) = ?\n");
		query.append("  AND CAT.tipo = ?\n");
		query.append("  AND CAT.id_usuario = ?\n");
		query.append("ORDER BY MOV.id\n");
		
		String [] parametros =  {new SimpleDateFormat("yyyy-MM").format(data) , 
								 String.valueOf(CategoryType.INCOME.ordinal()),
								 UsuarioUtils.getIdUsuario().toString()};
		
		Cursor cursor = db.rawQuery(query.toString(), parametros) ;
		
		if (cursor.moveToFirst( )) {
			CategoriasDAO categoriaDAO = new CategoriasDAO(contexto);
			do {
				try {
					IncomeVO entrada = new IncomeVO( ) ;
					entrada.setId( cursor.getInt( 0 ) ) ;
					entrada.setCategory( categoriaDAO.selectById(cursor.getInt( 1 )) ) ;
					entrada.setValue( cursor.getDouble( 2 ) ) ;
					entrada.setSaldo( cursor.getDouble( 3 ) ) ;
					entrada.setOperationDate( FORMATA_DATA.parse(cursor.getString( 4 )) ) ;
					entrada.setDescription( cursor.getString( 5 ) ) ;
					entrada.setLatitude(cursor.getDouble(6));
					entrada.setLongitude(cursor.getDouble(7));					
					list.add( entrada ) ;					
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
			while (cursor.moveToNext( )) ;
		}
		
		if (null != cursor && !cursor.isClosed( )) {
			cursor.close( ) ;
		}
		
		return list ;
	}
	
	public List<IncomeVO> getEntradasPorDia( Date data ) {
		List<IncomeVO> list = new ArrayList<IncomeVO>( ) ;
		StringBuilder query = new StringBuilder();
		query.append("SELECT MOV.id, MOV.id_categoria, MOV.valor, MOV.saldo, MOV.data, MOV.observacao, MOV.latitude, MOV.longitude\n");
		query.append("FROM MOVIMENTOS MOV\n");
		query.append("JOIN CATEGORIAS CAT ON CAT.id = MOV.id_categoria\n");
		query.append("WHERE data = ?\n");
		query.append("  AND CAT.tipo = ?\n");		
		query.append("  AND CAT.id_usuario = ?\n");		
		query.append("ORDER BY MOV.id\n");
		
		String [] parametros =  {MovimentosDAO.FORMATA_DATA.format(data) , 
								 String.valueOf(CategoryType.INCOME.ordinal()),
								 UsuarioUtils.getIdUsuario().toString()};
		
		Cursor cursor = db.rawQuery(query.toString(), parametros) ;
		
		if (cursor.moveToFirst( )) {
			CategoriasDAO categoriaDAO = new CategoriasDAO(contexto);
			do {
				try {
					IncomeVO entrada = new IncomeVO( ) ;
					entrada.setId( cursor.getInt( 0 ) ) ;
					entrada.setCategory( categoriaDAO.selectById(cursor.getInt( 1 )) ) ;
					entrada.setValue( cursor.getDouble( 2 ) ) ;
					entrada.setSaldo( cursor.getDouble( 3 ) ) ;
					entrada.setOperationDate( FORMATA_DATA.parse(cursor.getString( 4 )) ) ;
					entrada.setDescription( cursor.getString( 5 ) ) ;
					entrada.setLatitude(cursor.getDouble(6));
					entrada.setLongitude(cursor.getDouble(7));					
					list.add( entrada ) ;					
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
			while (cursor.moveToNext( )) ;
		}
		
		if (null != cursor && !cursor.isClosed( )) {
			cursor.close( ) ;
		}
		
		return list ;
	}
	
	public List<IncomeVO> getEntradasAgrupagasPorTipoDeCategoriaComSaldo( ) {
		List<IncomeVO> list = new ArrayList<IncomeVO>( ) ;

		StringBuilder query = new StringBuilder();
		query.append("SELECT CRE.id_categoria, SUM(CRE.valor) valor, SUM(CRE.saldo) saldo, CAT.descricao\n");
		query.append("FROM MOVIMENTOS CRE\n");
		query.append("INNER JOIN CATEGORIAS CAT ON CAT.id = CRE.id_categoria\n");
		query.append("WHERE CAT.tipo = ?\n");
		query.append("  AND CAT.id_usuario = ?\n");
		query.append("  AND CRE.saldo > 0\n");
		query.append("GROUP BY CRE.id_categoria, CAT.descricao\n");
		
		String[] parametros = {	String.valueOf(CategoryType.INCOME.ordinal()),
								UsuarioUtils.getIdUsuario().toString()};
		
		Cursor cursor = this.db.rawQuery(query.toString(), parametros);
		
		if (cursor.moveToFirst( )) {
			CategoriasDAO categoriaDAO = new CategoriasDAO(contexto);
			do {
				IncomeVO movimento = new IncomeVO( ) ;
				movimento.setCategory( categoriaDAO.selectById(cursor.getInt( 0 )) ) ;
				movimento.setValue( cursor.getDouble( 1 ) ) ;
				movimento.setSaldo( cursor.getDouble( 2 ) ) ;
				movimento.setDescription( cursor.getString( 3 ) ) ;
				list.add( movimento ) ;
			}
			while (cursor.moveToNext( )) ;
		}
		
		if (null != cursor && !cursor.isClosed( )) {
			cursor.close( ) ;
		}
		
		return list ;
	}
	
	public List<IncomeVO> getEntradasPorValorNaCategoria( Integer IdCategoria, Double valor ) {
		List<IncomeVO> list = new ArrayList<IncomeVO>( ) ;

		StringBuilder query = new StringBuilder();
		query.append("SELECT MOV.id, MOV.id_categoria, MOV.valor, MOV.saldo, MOV.data, MOV.observacao, MOV.latitude, MOV.longitude\n");
		query.append("FROM MOVIMENTOS MOV\n");
		query.append("JOIN CATEGORIAS CAT ON CAT.id = MOV.id_categoria\n");
		query.append("WHERE CAT.id = ?\n");			
		query.append("ORDER BY MOV.data, MOV.id\n");
		
		String [] parametros =  {IdCategoria.toString()};
		
		Cursor cursor = db.rawQuery(query.toString(), parametros) ;
		
		if (cursor.moveToFirst( )) {
			Double soma = 0.0;
			CategoriasDAO categoriaDAO = new CategoriasDAO(contexto);
			do {
				try {
					IncomeVO entrada = new IncomeVO( ) ;
					entrada.setId( cursor.getInt( 0 ) ) ;
					entrada.setCategory( categoriaDAO.selectById(cursor.getInt( 1 )) ) ;
					entrada.setValue( cursor.getDouble( 2 ) ) ;
					entrada.setSaldo( cursor.getDouble( 3 ) ) ;
					entrada.setOperationDate( FORMATA_DATA.parse(cursor.getString( 4 )) ) ;
					entrada.setDescription( cursor.getString( 5 ) ) ;
					entrada.setLatitude(cursor.getDouble(6));
					entrada.setLongitude(cursor.getDouble(7));					
					list.add( entrada ) ;					
					soma += entrada.getSaldo();
					if(soma > valor){
						break;
					}
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
			while (cursor.moveToNext( )) ;
		}
		
		if (null != cursor && !cursor.isClosed( )) {
			cursor.close( ) ;
		}
		
		return list ;
	}
	
	public Double getValorTotalEntradasPorMes( Date data  ) {

		StringBuilder query = new StringBuilder();
		query.append("SELECT SUBSTR(data,1,7), SUM(CRE.valor) valor\n");
		query.append("FROM MOVIMENTOS CRE\n");
		query.append("INNER JOIN CATEGORIAS CAT ON CAT.id = CRE.id_categoria\n");
		query.append("WHERE CAT.tipo = ?\n");
		query.append("  AND CAT.id_usuario = ?\n");
		query.append("  AND  SUBSTR(data,1,7) = ?\n");
		query.append("GROUP BY SUBSTR(data,1,7)\n");
		
		String[] parametros = {	String.valueOf(CategoryType.INCOME.ordinal()),
								UsuarioUtils.getIdUsuario().toString(),
								new SimpleDateFormat("yyyy-MM").format(data)};
		
		Cursor cursor = this.db.rawQuery(query.toString(), parametros);
		Double valor = 0.0;
		
		if (cursor.moveToFirst( )) {
			valor = cursor.getDouble( 1 );
		}
		
		if (null != cursor && !cursor.isClosed( )) {
			cursor.close( ) ;
		}
		
		return valor;
	}
	
	public Double getSaldoTotalEntradasPorMes( Date data  ) {

		StringBuilder query = new StringBuilder();
		query.append("SELECT SUBSTR(data,1,7), SUM(CRE.saldo) valor\n");
		query.append("FROM MOVIMENTOS CRE\n");
		query.append("INNER JOIN CATEGORIAS CAT ON CAT.id = CRE.id_categoria\n");
		query.append("WHERE CAT.tipo = ?\n");
		query.append("  AND CAT.id_usuario = ?\n");
		query.append("  AND  SUBSTR(data,1,7) = ?\n");
		query.append("GROUP BY SUBSTR(data,1,7)\n");
		
		String[] parametros = {	String.valueOf(CategoryType.INCOME.ordinal()),
								UsuarioUtils.getIdUsuario().toString(),
								new SimpleDateFormat("yyyy-MM").format(data)};
		
		Cursor cursor = this.db.rawQuery(query.toString(), parametros);
		Double valor = 0.0;
		
		if (cursor.moveToFirst( )) {
			valor = cursor.getDouble( 1 );
		}
		
		if (null != cursor && !cursor.isClosed( )) {
			cursor.close( ) ;
		}
		
		return valor;
	}	

	public Double getQuantidadeEntradasPorMes( Date data  ) {

		StringBuilder query = new StringBuilder();
		query.append("SELECT SUBSTR(data,1,7), Count(CRE.saldo) valor\n");
		query.append("FROM MOVIMENTOS CRE\n");
		query.append("INNER JOIN CATEGORIAS CAT ON CAT.id = CRE.id_categoria\n");
		query.append("WHERE CAT.tipo = ?\n");
		query.append("  AND CAT.id_usuario = ?\n");
		query.append("  AND  SUBSTR(data,1,7) = ?\n");
		query.append("GROUP BY SUBSTR(data,1,7)\n");
		
		String[] parametros = {	String.valueOf(CategoryType.INCOME.ordinal()),
								UsuarioUtils.getIdUsuario().toString(),
								new SimpleDateFormat("yyyy-MM").format(data)};
		
		Cursor cursor = this.db.rawQuery(query.toString(), parametros);
		Double valor = 0.0;
		
		if (cursor.moveToFirst( )) {
			valor = cursor.getDouble( 1 );
		}
		
		if (null != cursor && !cursor.isClosed( )) {
			cursor.close( ) ;
		}
		
		return valor;
	}	
}